﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using ChinaDynastyTours.Models;
using System.Data.Common;

namespace ChinaDynastyTours.Controls
{
    class LocalTeamGroupCtrl
    {
        #region 行程计划数据Copy到本地行程计划

        /// <summary>
        /// 拷贝属于龙润旅行社的行程计划到本地行程计划中
        /// </summary>
        /// <param name="scheduleIds">以‘|’分割的行程计划ID</param>
        /// <returns></returns>
        public static int CopyTourGroupSchedule2LocalSchedule(string scheduleIds,string ownerProviderId)
        {
            string[] scheduleIdArray = scheduleIds.Split(new char[]{ '|'});

            StringBuilder Sql = new StringBuilder();
            Sql.AppendFormat(@"insert into Sch_ProvidersPlanSchedule
                                        ([TourGroupScheduleID],[TourgroupID], [Date],[Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType],[TravelAgencyID], [TravelAgencyName], [Reception], [Context], [Hotel],[HotelID],[Seat],[PageNumber],[OwnerProviderID])
                                        select [TourGroupScheduleID],[TourgroupID], [Date],[Time], [StayOver], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [TicketType],[TravelAgencyID], [TravelAgencyName], [Reception], [Context], [Hotel],[HotelID],[Seat],1,'{0}' from orgt_TourGroupSchedule ",ownerProviderId);//去处了‘用餐列’[Dining]

            Sql.Append(" WHERE TourGroupScheduleID=");
            for (int i = 1; i < scheduleIdArray.Length; i++)
            {
                Sql.AppendFormat("{0} ", scheduleIdArray[i]);
                if (i != scheduleIdArray.Length-1)
                    Sql.AppendFormat(" or tourgroupscheduleid=");
            }

            Database db = DatabaseFactory.CreateDatabase();

            return db.ExecuteNonQuery(CommandType.Text, Sql.ToString());
        }
        /// <summary>
        /// 拷贝团备注到本地备注中
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        public static int CopyTourGroupRemark2LocalRemark(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE orgt_TourGroup SET LocalRemark=Remark WHERE TourgroupID='{0}'", tourGroupId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteNonQuery(dbCommand);
        }
        #endregion

        #region 行程计划数据的获取

        /// <summary>
        /// 獲取地接社的‘操作号’
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        internal static object GetOperationNumberForTravel(string tourGroupId, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT OperationNumber FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND OwnerProviderID='{1}'", tourGroupId,ownerProviderId);
            dbCommand = db.GetSqlStringCommand(sql);

            return db.ExecuteScalar(dbCommand);
        }
       
        public static DataTable GetLocalPlanScheduleForPrint(string tourGroupID, int pageNumber, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Dining],[Hotel] FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}' AND PageNumber={1} AND OwnerProviderID='{2}' order by [Date],[Time]", tourGroupID, pageNumber, ownerProviderId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataTable GetLocalPlanScheduleForBillPrint(string tourGroupID, int pageNumber, string ownerProviderId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT [Date], [Time], [Journeytype], [StartCity], [DestinationCity], [TrafficType], [Traffic], [Context],[Dining],[Hotel] FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}' AND PageNumber={1} AND OwnerProviderID='{2}' AND [Journeytype]<>'' order by [Date],[Time]", tourGroupID, pageNumber, ownerProviderId);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        //internal static object GetLocalPlanScheduleCiceroniIDByPageNumber(string tourgroupId, int pageNumber, string ownerProviderId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT CiceroniID FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND PageNumber={1} AND OwnerProviderID='{2}'", tourgroupId, pageNumber, ownerProviderId);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    return db.ExecuteScalar(dbCommand);
            
        //}
        
        public static int GetLocalPlanSchedulePageNumbers(string tourgroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string sql = string.Format("SELECT Max(PageNumber) FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}'", tourgroupId);
            dbCommand = db.GetSqlStringCommand(sql);

            object obj = db.ExecuteScalar(dbCommand);
            return obj == DBNull.Value ? 0 : int.Parse(obj.ToString());
        }
        
        internal static DataTable GetLocalPlanSchedule(string tourGroupID,string loginProviderID, int pageNumber)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT * FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND OwnerProviderID='{1}' AND PageNumber={2} ORDER BY [Date],[Time]", tourGroupID, loginProviderID, pageNumber);
            DbCommand dbCommand = db.GetSqlStringCommand(sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        public static DataSet GetLocalPlanSchedule(string tourGroupID, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format("SELECT * FROM Sch_ProvidersPlanSchedule WHERE TourgroupID='{0}'  AND OwnerProviderID='{1}'", tourGroupID, loginProviderID);

            return db.ExecuteDataSet(CommandType.Text, sql);
        }

        /// <summary>
        /// 获取供应商清单（按或不按日程显示）
        /// </summary>
        /// <param name="Id">byLocalPLanScheduleID为true则id为本地行程计划号，否则id为团号</param>
        /// <param name="byLocalPLanScheduleID"></param>
        /// <returns></returns>
        //public static DataTable GetRestaurantOrderForProvider(string Id, int pNum, bool byLocalPLanScheduleID)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    string sql="";
        //    if(byLocalPLanScheduleID)//按日程显示
        //    {     
        //        sql = string.Format( @"SELECT [RestaurantOrderID],[ServiceName], [ProviderName] FROM ord_RestaurantOrder WHERE localPlanScheduleId={0}", Id);
        //    }
        //    else{
        //        sql = string.Format(@"SELECT en.[RestaurantOrderID], en.[ServiceName], en.[ProviderName] FROM ord_RestaurantOrder en, Sch_ProvidersPlanSchedule lp WHERE  (en.localPlanScheduleId=lp.LocalPlanScheduleID) AND lp.TourgroupID='{0}'AND lp.PageNumber='{1}'", Id, pNum);
        //    }
            
        //    DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);

        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}

        #endregion

        #region 行程计划有关数据增、删、改
        /// <summary>
        /// 行程计划数据创建
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public static int CreateLocalTourGroupSchedule(ProvidersPlanSchedule entity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"INSERT INTO Sch_ProvidersPlanSchedule([TourgroupID], [Date], [Time], [StayOver], [Journeytype], [StartCity],
                                                                           [DestinationCity], [TrafficType], [Traffic], [TicketType], [TravelAgencyName],
                                                                           [Reception], [Context], [Dining], [Hotel],[TravelAgencyID],[HotelID],[TourGroupScheduleID],
                                                                           [Seat],[PageNumber],[OwnerProviderID])
                                                                           VALUES('{0}', '{1}', '{2}', {3}, '{4}','{5}','{6}', '{7}', '{8}','{9}','{10}','{11}','{12}','{13}','{14}',{15},{16},{17},'{18}',{19},'{20}')",
                                                                            entity.TourgroupID,
                                                                            entity.Date,
                                                                            entity.Time,
                                                                            entity.StayOver,
                                                                            entity.Journeytype,
                                                                            entity.StartCity,
                                                                            entity.DestinationCity,
                                                                            entity.TrafficType,
                                                                            entity.Traffic,
                                                                            entity.TicketType,
                                                                            entity.TravelAgencyName,
                                                                            entity.Reception,
                                                                            entity.Context,
                                                                            entity.Dining,
                                                                            entity.Hotel,
                                                                            entity.TravelAgencyID,
                                                                            entity.HotelID,
                                                                            entity.TourGroupScheduleID,
                                                                            entity.Seat,
                                                                            entity.PageNumber,
                                                                            entity.OwnerProviderID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 根据行程计划号，更新行程计划数据
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public static int UpdateLocalTourGroupSchedule(ProvidersPlanSchedule scheduleData)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET
                                        [TourgroupID]='{0}',
                                        [Date]='{1}',                                      [Time]='{2}',
                                        [StayOver]={3},                                    [Journeytype]='{4}',
                                        [StartCity]='{5}',                                 [DestinationCity]='{6}',
                                        [TrafficType]='{7}',                               [Traffic]='{8}',
                                        [TicketType]='{9}',                                [TravelAgencyName]='{10}',
                                        [Reception]='{11}',                                [Context]='{12}',
                                        [Dining]='{13}',                                   [Hotel]='{14}',
                                        [TravelAgencyID]={15},                             [HotelID]={16},
                                        [OperationNumber]='{17}'
                                        WHERE LocalPlanScheduleID={18}",
                                        scheduleData.TourgroupID,
                                        scheduleData.Date,
                                        scheduleData.Time,
                                        scheduleData.StayOver,
                                        scheduleData.Journeytype,
                                        scheduleData.StartCity,
                                        scheduleData.DestinationCity,
                                        scheduleData.TrafficType,
                                        scheduleData.Traffic,
                                        scheduleData.TicketType,
                                        scheduleData.TravelAgencyName,
                                        scheduleData.Reception,
                                        scheduleData.Context,
                                        scheduleData.Dining,
                                        scheduleData.Hotel,
                                        scheduleData.TravelAgencyID,
                                        scheduleData.HotelID,
                                        scheduleData.OperationNumber,
                                        scheduleData.LocalPlanScheduleID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
//        internal static void UpdateLocalTourGroupScheduleCiceroniData(string tourgroupId, int pageNumber, string ciceroniId, string ciceroniName, string ciceroniPhone)
//        {
//            //if (string.IsNullOrEmpty(ciceroniId)) return;

//            Database db = DatabaseFactory.CreateDatabase();
//            string sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET [CiceroniID]={0},[CiceroniName]='{1}',[Phone]='{2}' 
//                                         WHERE TourgroupID='{3}' AND PageNumber={4}", ciceroniId, ciceroniName, ciceroniPhone, tourgroupId, pageNumber);
//            db.ExecuteNonQuery(CommandType.Text, sql);
//        }

        /// <summary>
        /// 更新地接社的‘操作号’
        /// </summary>
        /// <param name="scheduleData"></param>
        /// <returns></returns>
        public static int UpdateOperationNumberForTravel(string tourgroupId,string operationNumber,string ownerProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET [OperationNumber]='{0}' WHERE TourgroupID='{1}' AND OwnerProviderID='{2}'", operationNumber, tourgroupId,ownerProviderID);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 更新本地行程计划的页顺序
        /// </summary>
        /// <param name="tourgroupId">计划号</param>
        /// <param name="pageNumber">当前的页号</param>
        internal static void UpdateLocalTourGroupSchedulePageNumber(string tourgroupId, int pageNumber)
        {
            Database db = DatabaseFactory.CreateDatabase();
            //查找大于删除页的页号
            string sql = string.Format("SELECT PageNumber FROM Sch_ProvidersPlanSchedule WHERE TourGroupID='{0}' AND PageNumber>{1} GROUP BY PageNumber ORDER BY PageNumber ASC", tourgroupId, pageNumber);
            DataSet ds = db.ExecuteDataSet(CommandType.Text, sql);

            //将后续页号提前
            if (ds.Tables.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    sql = string.Format(@"UPDATE Sch_ProvidersPlanSchedule SET PageNumber=PageNumber-1 WHERE  TourGroupID='{0}' AND PageNumber={1}", tourgroupId, dr["PageNumber"]);
                    db.ExecuteNonQuery(CommandType.Text, sql);
                }
            }
        }
        /// <summary>
        /// 删除指定计划号行程数据数据
        /// </summary>
        /// <param name="tourGroupScheduleID"></param>
        /// <returns></returns>
        public static int DeleteLocalTourGroupSchedule(int localPlanScheduleId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM Sch_ProvidersPlanSchedule WHERE LocalPlanScheduleID={0}", localPlanScheduleId);
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

          
        #endregion    
    

    }
}
